package com.nexttwist.common;

public class DBContants {
	public static String ADD_STORY = "INSERT INTO story (`storyid`, `storyname`, `crtdbyid`, `delflag`, `crtdtime`, `type`)VALUES ( ? , ? , ? , 'N', ?, ?)";
	public static String ADD_STORY_LINE = "INSERT INTO `storyline` (`storyid`, `storylineid`, `storyline`,`totalvote`, `crtdbyid`, `delflag`, `crtdtime`, `type` ) VALUES ( ? , ?, ? , ? , ? , 'N', ? , ?)";
	public static String ADD_ENTRY_LINE = "INSERT INTO `entryline` (`storyid`, `storylineid`,`entrylineid`, `entryline`, `crtdbyid`, `delflag`, `crtdtime`, `type` ) VALUES ( ? ,?, ?, ? , ? , 'N', ?, ? )";
	public static String ADD_REPORT_FLAG = "insert into flag (`uniqueentrylineid`,`storyid`,`linecrtdbyid`,`flaggivenid`,`delflag`,`ctrdtime`) values ( ? , ? , ? , ? ,'N', ? )";
	public static String ADD_ENTRY_LINE_VOTE = "insert into entrylinevote (`storyid`, `entrylineid`, `votebyuserid`, `vote`, `crtdbyid`, `delflag`,`crtdtime`, `type`) values ( ? , ? , ? , ? , ? ,'N', ?, ? )";
	public static String ADD_NOTIFICATION = "INSERT INTO notification (followerid,followingid,storyid,storylineid,uniqueentryid,delflag,readflag,event,crtdtime, `type`) VALUES (? , ? , ? , ? , ? ,'N','N', ? , ?, ? )";
	
	public static String SELECT_VOTE_COUNT_ENTRYLINE = "SELECT entrylineid from entrylinevote where entrylineid = ? and delflag = 'N' and vote = ?";
	public static String SELECT_STORY_TIME = "SELECT crtdtime FROM storyline WHERE storylineid = ? AND delflag = 'N'";
	public static String SELECT_ENTRYLINE_TIME = "SELECT crtdtime FROM entryline WHERE displayseq = ? AND delflag = 'N'";
	public static String MAX_ENTRYLINE_ID = "select max(entrylineid) as eid from entryline where storyid = ? and storylineid = ?";
	public static String MAX_STORYLINE_ID = "select max(storylineid) as sid from storyline where storyid = ?";
	public static String MAX_STORY_ID = "select max(storyid) as sid from story";
	public static String MAX_USER_ID = "select max(userid) as uid from users";
	public static String GET_USER_DETAILS_BYNAME = "SELECT * FROM users WHERE username = ? AND `delete flag` = 'N'";
	public static String GET_USER_DETAILS_BYID = "SELECT * FROM `users` WHERE `userid` = ? and `delete flag` = 'N' LIMIT 1";
	public static String GET_USER_DETAILS_BYEMAIL = "SELECT * FROM `users` WHERE `email` = ? and `delete flag` = 'N' LIMIT 1";
	public static String GET_STORYLINE_DETAILS = "SELECT * FROM storyline WHERE storylineid = ? AND storyid = ? AND delflag = 'N'";
	public static String GET_ENTRYLINE_USER = "SELECT crtdbyid FROM entryline WHERE displayseq = ?";
	public static String GET_STORYLINE_BY_STORYID = "SELECT * from storyline WHERE storyid = ? and delflag = 'N' ORDER BY storylineid ASC";
	public static String GET_ENTRYLINE = "SELECT * FROM entryline WHERE storyid = ? and delflag = 'N' and storylineid = ?";
	public static String GET_ENTRYLINE_BYID = "SELECT * FROM entryline where displayseq = ? and delflag = 'N'";
	public static String GET_VOTES = "SELECT vote from entrylinevote WHERE storyid = ? and entrylineid = ? and delflag = 'N' and vote = ?";
	public static String GET_VOTE_BY_USERID = "SELECT vote from entrylinevote WHERE storyid = ? and entrylineid = ?  and delflag = 'N' and votebyuserid = ?";
	public static String GET_VOTE_BY_ENTRYLINEID = "update entrylinevote set delflag = 'Y' WHERE entrylineid = ? and delflag = 'N'";
	public static String GET_FLAG = "SELECT storyid FROM flag WHERE uniqueentrylineid = ? AND delflag = 'N'";
	public static String GET_ALL_STORIES = "SELECT * from story where delflag = 'N'";
	public static String VOTE_COUNT_FOR_HOT_STORY = "select count(entrylinevote.vote) as votes, count(distinct(entrylineid)) as entryid from entrylinevote where storyid = ? and delflag = 'N'";
	public static String USER_COUNT_IN_STORY = "select count(distinct(crtdbyid)) as uid from entrylinevote where storyid = ? and delflag = 'N'";
	public static String GET_FRESH_STORY = "select DISTINCT(a.storyid) as sid from story a, entryline b, entrylinevote c WHERE a.storyid = b.storyid AND a.storyid = c.storyid ORDER BY c.crtdtime DESC, b.crtdtime DESC, a.crtdtime DESC";
	public static String GET_STORY_BY_ID = "select * from story where delflag = 'N' AND storyid=?";
	
	public static String GET_FOLLOWERS = "SELECT followerid FROM followme WHERE followingid = ? and delflag = 'N'";
	
	public static String DELETE_NOTIFICATION = "UPDATE notification SET delflag = 'Y' WHERE followerid = ? AND followingid = ? and storyid = ? and storylineid = ? and uniqueentryid = ? AND delflag = 'N'";
	public static String DELETE_STORY = "UPDATE story SET delflag = 'Y' WHERE storyid = ? and delflag = 'N'";
	public static String DELETE_STORYLINE = "UPDATE storyline SET delflag = 'Y' WHERE storyid = ? and delflag = 'N'";
	public static String DELETE_ENTRYLINE = "UPDATE entryline SET delflag = 'Y' WHERE storyid = ? and delflag = 'N'";
	public static String DELETE_ENTRYLINE_BYID = "UPDATE entryline SET delflag = 'Y' WHERE `storyid` = ? and displayseq = ? and delflag = 'N'";
	public static String DELETE_VOTE = "UPDATE entrylinevote SET delflag = 'Y' WHERE storyid = ? and delflag = 'N'";
	public static String DELETE_VOTE_BYUSERID = "update entrylinevote set delflag = 'Y' where entrylineid = ? and votebyuserid = ? and delflag = 'N'";
	public static String DELETE_NOTIFICATION_BYSTORYID = "UPDATE notification SET delflag = 'Y' WHERE storyid = ? and delflag = 'N'";
	public static String DELETE_NOTIFICATION_BYENTRYLINEID = "UPDATE notification SET delflag = 'Y' WHERE storyid = ? AND uniqueentryid = ? and delflag = 'N'";
	
	public static String UPDATE_STORY = "UPDATE story SET storyname = ? WHERE storyid = ? and delflag = 'N'";
	public static String UPDATE_STORYLINE = "UPDATE storyline SET storyline = ? WHERE storyid = ? and delflag = 'N' and storylineid = 1";
	
	public static String STORYLINE_COUNT = "select storyid from storyline where crtdbyid = ? and storylineid != 1 and delflag = 'N'";
	
	//User related Queries
	public static String ADD_USER = "INSERT INTO users (userid,fbuid,ttrid,lnkid,username,pwd,fname,lname,email,DOB,`profile image path`,gender,`delete flag`,`time`,Isadmin,`email varification`) VALUES ( ? , ? , ? , ? , ? , SHA1(?) , ? , ? , ? , ? , ? , ? ,'N', NOW(), ? , ? )";
	public static String GET_USER_BY_USERID = "SELECT * from users WHERE userid = ? and `delete flag` = 'N'";
	public static String UPDATE_USER_DELETE_FLAG = "UPDATE users SET `delete flag` = 'Y' WHERE userid = ? and `delete flag` = 'N'";
	public static String GET_USER_BY_USERID_AND_PASSWORD = "SELECT * from users WHERE userid = ? and pwd = SHA1(?) and `delete flag` = 'N'";
	public static String ADD_FOLLOWME = "INSERT INTO followme (followerid,followingid,delflag,crtdtime) VALUES ( ? , ? ,'N',NOW())";
	public static String GET_FOLLOWME_DETAILS = "SELECT * from followme WHERE followerid = ? and followingid = ? and delflag = 'N'";
	public static String GET_LIST_OF_FOLLOWERS = "SELECT * FROM followme, users WHERE users.userid = followme.followingid AND followme.followerid = ? AND followme.delflag = 'N' AND `delete flag` = 'N' ORDER BY users.username limit ? , ?";
	public static String GET_LIST_OF_FOLLOWINGS = "SELECT * FROM followme, users WHERE users.userid = followme.followerid AND followme.followingid = ? AND followme.delflag = 'N' AND `delete flag` = 'N' ORDER BY users.username limit ? , ?";
	public static String USER_LOGIN = "SELECT * from users WHERE email = ? and pwd = SHA1(?) and `delete flag` = 'N' AND `email varification` = 'Y' LIMIT 1";
	public static String GET_STORY = "SELECT DISTINCT a.storyid as sid, a.storyname as stitle, a.crtdtime as time FROM story a WHERE crtdbyid = ? and delflag = 'N' ORDER BY time DESC LIMIT ?, ?";
	public static String GET_STORY_LINES  = "SELECT storyid FROM storyline WHERE storyid = ?";
	public static String NO_OF_VOTES = "SELECT count(storyid) as votes FROM entrylinevote WHERE storyid = ? AND delflag = 'N'";
}


